package com.hellogood.yaohuo.server.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.hellogood.yaohuo.server.db.ConnectionPool;
import com.hellogood.yaohuo.server.db.DBHelper;
import com.hellogood.yaohuo.server.mgr.factory.MgrFactory;
import com.hellogood.yaohuo.shared.data.CallDTO;
import com.hellogood.yaohuo.shared.entity.YsDetail;
import com.hellogood.yaohuo.shared.entity.YsForm;
import com.hellogood.yaohuo.shared.exception.DatabaseException;
import com.hellogood.yaohuo.shared.vo.MainFrame;

public class YsFormDAO extends DBHelper {
	Logger log = Logger.getLogger(this.getClass());

	@Override
	public List<?> initializeObject(ResultSet rs) throws SQLException {
		List<YsForm> ysFormList = new ArrayList<YsForm>();
		if (rs != null) {
			while (rs.next()) {
				YsForm ysForm = new YsForm();
				ysForm.setYsBillNo(rs.getString("YsBillNo"));
				ysForm.setYsDate(rs.getString("YsDate"));
				ysForm.setOrgCode(rs.getString("OrgCode"));
				ysForm.setVendorCode(rs.getString("VendorCode"));
				ysForm.setVendorName(rs.getString("Vendorname"));
				ysForm.setCounts(rs.getInt("Counts"));
				ysForm.setCost(rs.getBigDecimal("Cost"));
				ysForm.setNetCost(rs.getBigDecimal("NetCost"));
				ysForm.setTotal(rs.getBigDecimal("Total"));
				ysForm.setStaffCode(rs.getString("StaffCode"));
				ysForm.setStaffName(rs.getString("StaffName"));
				ysForm.setTimeMark(rs.getString("TimeMark"));
				ysForm.setRzDate(rs.getString("RzDate"));
				ysForm.setRemark(rs.getString("Remark"));
				ysFormList.add(ysForm);
			}
		}
		return ysFormList;
	}

	public String getLastYsBillNo(String billNo) {
		String sql = "SELECT top 1 * FROM YsForm LIKE '" + billNo + "%' ORDER BY Ysbillno desc";
		List<?> list = super.query(sql);
		if (list == null || list.isEmpty()) {
			return null;
		}
		YsForm form = (YsForm) list.get(0);
		return form.getYsBillNo();

	}

	public String createYanshou(YsForm form, boolean isModify) {
		String result = "";
		List<String> sqls = new ArrayList<String>();
		if (isModify) {
			String sql = "DELETE FROM YsForm WHERE ysBillNo = '" + form.getYsBillNo() + "'";
			sqls.add(sql);
			sql = "DELETE FROM YsDetail WHERE ysBillNo = '" + form.getYsBillNo() + "'";
			sqls.add(sql);
		}
		String sql = "INSERT INTO YsForm(ysBillNo,ysDate,orgCode,vendorCode,vendorName,counts,cost,netCost,total,"
				+ "staffCode,staffName,rzDate,remark,createTime) VALUES('"
				+ form.getYsBillNo()
				+ "',CONVERT(varchar(100), GETDATE(), 23),'"
				+ form.getOrgCode()
				+ "','"
				+ form.getVendorCode()
				+ "','"
				+ form.getVendorName()
				+ "',"
				+ form.getCounts()
				+ ","
				+ form.getCost()
				+ ","
				+ form.getNetCost()
				+ ","
				+ form.getTotal()
				+ ",'"
				+ form.getStaffCode() + "','" + form.getStaffName() + "','" + form.getRzDate() + "','" + form.getRemark() + "',getDate())";
		sqls.add(sql);

		List<YsDetail> list = form.getList();
		if (list != null && (!list.isEmpty())) {
			for (YsDetail d : list) {
				sql = "INSERT INTO YsDetail(ysBillNo,pluCode,pluName,barCode,CSize,depCode,depName,Bra,Color,jPrice,netJPrice,price,counts,"
						+ "cost,netCost,total,rzDate,remark,orgCode) VALUES('"
						+ d.getYsBillNo()
						+ "','"
						+ d.getPluCode()
						+ "','"
						+ d.getPluName()
						+ "','"
						+ d.getBarCode()
						+ "','"
						+ d.getSize()
						+ "','"
						+ d.getDepCode()
						+ "','"
						+ d.getDepName()
						+ "','"
						+ d.getBra()
						+ "','"
						+ d.getColor()
						+ "',"
						+ d.getjPrice()
						+ ","
						+ d.getPrice()
						+ ","
						+ d.getPrice()
						+ ","
						+ d.getCounts()
						+ ","
						+ d.getCost()
						+ ","
						+ d.getCost()
						+ ","
						+ d.getTotal()
						+ ",'"
						+ d.getRzDate() + "','" + d.getRemark() + "','" + d.getOrgCode() + "')";
				sqls.add(sql);
			}
		}
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return e.getMessage();
		}
		return result;
	}

	@SuppressWarnings("unchecked")
	public List<YsForm> searchYsFormByQuyu(String quyuCode, String startDate, String endDate, String rz) {
		String userCode = MainFrame.currentUser.getStaffCode();
		String orgs = "";
		if (quyuCode == null) {
			orgs = MgrFactory.getInstance().createOrgMgr().getOrgCodeByUser(userCode);
		} else {
			orgs = MgrFactory.getInstance().createOrgMgr().getOrgCodeByUserQuyu(userCode, quyuCode);
		}
		String sql = "SELECT * FROM YsForm WHERE OrgCode IN (?) AND cast(YsDate as char(10)) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,?),120) AND cast(YsDate as char(10)) <= CONVERT(VARCHAR(10),CONVERT(DATETIME,?),120)";
		if (rz.equals("已入账")) {
			sql = sql + " AND rzDate <> ''";
		} else if (rz.equals("未入账")) {
			sql = sql + " AND rzDate = ''";
		}
		Object[] args = new Object[] { orgs, startDate, endDate };
		List<?> list = super.query(sql, args);
		return (List<YsForm>) list;
	}

	@SuppressWarnings("unchecked")
	public List<YsForm> searchYsForm(String orgCode, String startDate, String endDate, String rz) {
		String sql = "SELECT * FROM YsForm WHERE OrgCode = ? AND cast(YsDate as char(10)) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,?),120) AND cast(YsDate as char(10)) <= CONVERT(VARCHAR(10),CONVERT(DATETIME,?),120)";
		if (rz.equals("已入账")) {
			sql = sql + " AND rzDate <> ''";
		} else if (rz.equals("未入账")) {
			sql = sql + " AND rzDate = ''";
		}
		Object[] args = new Object[] { orgCode, startDate, endDate };
		List<?> list = super.query(sql, args);
		return (List<YsForm>) list;
	}

	public YsForm getFormByBillNo(String billNo) {
		String sql = "SELECT * FROM ysForm WHERE ysBillNo = ?";
		Object[] args = new Object[] { billNo };
		List<?> list = super.query(sql, args);
		if (list != null && (!list.isEmpty())) {
			return (YsForm) list.get(0);
		}
		return null;
	}

	public String deleteYanshou(String billNo) {
		String result = "";
		List<String> sqls = new ArrayList<String>();

		String sql = "DELETE FROM ysForm WHERE ysBillNo = '" + billNo + "'";
		sqls.add(sql);
		sql = "DELETE FROM ysDetail WHERE ysBillNo = '" + billNo + "'";
		sqls.add(sql);
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			log.error(e.getMessage());
			e.printStackTrace();
			return e.getMessage();
		}
		return result;
	}

	public void rz(String bill) throws DatabaseException {
		CallDTO dto = new CallDTO();
		String sql = "UPDATE ysForm SET rzDate WHERE ysBillNo = '" + bill + "'";
		String call = "{CALL ysrz(?,?)}";
		dto.setBill(bill);
		dto.setCall(call);
		dto.setSql(sql);
		dto.setHxdbName(ConnectionPool.hxDbName);
		super.callProcedure(dto);
	}
}
